﻿

using Dapper;
using HBase;
using HBase.db;
using hCommand;
using Persistence.sqlserver;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Threading;

namespace Persistence.abs
{
    public abstract class AbsCoreWhLocServer : AbsWMSBaseServer, ICoreWhLocServer
    {

     
      
        public List<DapingDto> getDapingDto(int id) {
            String sql = "SELECT [loc_Id] l, [status] s  FROM  [Core_stock]  where status >0 ";
            if (id == 1) {
                sql += " and [loc_Id]<500000";
            } else if (id == 2) {
                sql += " and [loc_Id]>500000  and [loc_Id]<900000";
            }

            return readConn().Query<DapingDto>(sql).ToList();
        }

        #region 区域设置
        //区域添加
        public long AddArea(CoreWhArea model)
        { 
            return opConn().Insert<CoreWhArea>(model);
        }
        //区域修改
        public bool UpdateArea(CoreWhArea coreWhArea)
        {
            var old = opConn().GetById<CoreWhArea>(coreWhArea.id);
            if (old.priority != coreWhArea.priority) { 
                String sql2 = "update core_stock set  area_order =@areaOrder where  loc_Id in (select l.id from  core_wh_loc l  where l.area_Id=@areaId)";
                exec(sql2, new { areaId = coreWhArea.id, areaOrder= coreWhArea.priority });
            }
            return opConn().Update<CoreWhArea>(coreWhArea);
        }
        //区域删除
        public int DeleteArea(List<CoreWhArea> areaList)
        {
            foreach (var coreWhArea in areaList)
            {
                opConn().Delete<CoreWhArea>("id=" + coreWhArea.id.ToString());
            }
            return 1;
        }
        //通过区域ID查找
        public CoreWhArea FindAreaById(long id)
        {
            return readConn().Get<CoreWhArea>(id);
        }
        //区域名称查重
        public bool QueryAreaPageByName(CoreWhArea coreWhArea)
        {
            string sql = @"select * from Core_wh_Area where Area_Name='{0}'";
            sql = string.Format(sql, coreWhArea.AreaName);
            List<CoreWhArea> list = readConn().Query<CoreWhArea>(sql, coreWhArea).ToList();
            if (list.Count == 0)
                return false;
            else
                return true;
        }
        //区域分页显示
        public Page<CoreWhArea> QueryAreaPage(CoreWhAreaDto dto)
        {
            string sql = @"select ca.* from Core_wh_Area ca where 1=1";
            if (dto != null && !string.IsNullOrEmpty(dto.AreaName))
            {
                dto.AreaName = "%" + dto.AreaName + "%";
                sql += " AND ca.Area_Name like @AreaName";
            }
            if (dto != null && dto.whId != 0)
            {
                sql += " AND ca.wh_Id = @whId";
            }
            if (dto != null && !string.IsNullOrEmpty(dto.orderNo))
            {
                sql += " AND ca.order_No = @orderNo";
            }
            // Page<ClassQuery> info= queryPage<ClassQuery>(sql, "id", dto);
            return this.queryPage<CoreWhArea>(sql, "order_No desc ", dto);
        }
        //根据库房ID查询数据
      
        public List<CoreWhAreaStat> stat() {
            String sql = "SELECT a.id, Area_Name ,count(*) 'all',sum(Count_stock) 'use',max(a.order_No) order_No FROM `core_wh_area` a join core_wh_loc l on a.id=l.area_Id GROUP BY Area_Name ,a.id order by order_No  desc  ";
            return readConn().Query<CoreWhAreaStat>(sql).ToList();
        }
        #endregion

        #region 库位设置
        //库位分页
        public Page<CoreWhLoc> QueryLocPage(CoreWhLocDto dto)
        {
            string sql = @"select cl.id,cl.crane_Id,cl.Shelf_Id,
                           cl.area_Id,cl.Row_No,cl.Col_No,
                           cl.type,cl.order_No,cl.deep_Index,
                           cl.max_stock,
                           cl.Count_stock,cl.move_group,
                           cl.Active_Status
                           
                           from Core_wh_Loc cl where max_stock>0";
            if (dto != null)
            {
                if (dto != null && dto.areaId != 0)
                {
                    sql += " AND cl.area_Id=@areaId";
                }
                if (dto.EndRowNumber != 0 && dto.RowNumber != 0)
                {
                    sql += " AND cl.Row_No>=@RowNumber and cl.Row_No<=@EndRowNumber";
                }
                else if (dto.RowNumber != 0)
                {
                    sql += " AND cl.Row_No=@RowNumber";
                }

                if (dto.EndColNumber != 0 && dto.ColNumber != 0)
                {
                    sql += " AND cl.Col_No>=@ColNumber and cl.Col_No<=@EndColNumber";
                }
                else if (dto != null && dto.ColNumber != 0)
                {
                    sql += " AND cl.Col_No=@ColNumber";
                }

                if (dto != null && dto.ShelfId != 0)
                {
                    sql += " AND cl.shelf_Id = @ShelfId";
                }
                if (!string.IsNullOrEmpty(dto.ActiveStatus))
                {
                    sql += " AND cl.Active_Status = @ActiveStatus";
                }
            }

            return this.queryPage<CoreWhLoc>(sql, "id", dto);
        }
     

        
        //库位删除
        public int DeleteLoc(List<CoreWhLoc> locList)
        {
            foreach (var coreWhLoc in locList)
            {
                opConn().Delete<CoreWhLoc>("id=" + coreWhLoc.id.ToString());
            }
            return 1;
        }
        //通过库位ID查找信息
        public CoreWhLoc FindLocById(long id)
        {
            return readConn().Get<CoreWhLoc>(id);
        }
        //通过库位名称查找信息
        public bool QueryLocPageByRow(CoreWhLoc coreWhLoc)
        {
            string sql = @"select cl.* from Core_wh_Loc cl where cl.Row_No='{0}' and cl.Col_No='{1}'";
            sql = string.Format(sql, coreWhLoc.RowNo, coreWhLoc.ColNo);
            List<CoreWhArea> list = readConn().Query<CoreWhArea>(sql, coreWhLoc).ToList();
            if (list.Count == 0)
                return false;
            else
                return true;
        }
        //通过区域ID查找信息
        public List<CoreWhLoc> GetLocByWhAreaId(long areaId)
        {
            String sql = "select cl.* from Core_wh_Loc cl where cl.area_Id=@areaId";
            return readConn().Query<CoreWhLoc>(sql, new { areaId = areaId }).ToList();
        }
         
 
        #endregion
    }
}
